Imports System.Text
Imports System.Data.SqlClient

Friend Class DBScanExtendedProperties
    Inherits DBBase

    '---Add an extended property with a description to the database
    '  EXEC sys.sp_addextendedproperty 
    '@name = 'MS_Description', 
    '@value = 'This is a test database', 
    '@level0type = default, @level0name = default,
    '@level1type = default,  @level1name = default,
    '@level2type = default, @level2name = default


    Public Sub UpdateExtendedProperties( _
        ByVal lDatabaseID As Long)

        Dim oLoginFactory As New SQLAutoDocLib.Login_Factory
        Dim oTableFactory As New SQLAutoDocLib.Table_Factory
        Dim oViewFactory As New SQLAutoDocLib.View_Factory
        Dim oSProcFactory As New SQLAutoDocLib.SProc_Factory
        Dim oFunctionFactory As New SQLAutoDocLib.Function_Factory
        Dim oFieldFactory As New SQLAutoDocLib.Field_Factory

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        '---Get Database Description
        Dim oDBT As DataTable = GetDatabaseDescription(oDatabase.DecryptedConnectionString)
        If oDBT.Rows.Count > 0 Then
            oDatabase.Desc = oDBT.Rows(0).Item(0)
            oDatabase.Save()
        End If

        '---Get descriptions of logins
        Dim oLT As DataTable = GetLoginDescriptions(oDatabase.DecryptedConnectionString)
        For Each oRow As DataRow In oLT.Rows
            Dim oLogin As SQLAutoDocLib.Login = oLoginFactory.GetLoginByName(lDatabaseID, oRow.Item("objname"))
            If Not oLogin Is Nothing Then
                oLogin.Desc = oRow.Item("value")
                oLogin.Save()
            End If
        Next

        '---Get list of logins
        For Each oLogin As SQLAutoDocLib.Login In oLoginFactory.GetAllLoginsForDatabase(lDatabaseID)

            '---Get table descriptions for this login
            Dim oTBT As DataTable = GetTableDescriptions(oLogin.Login, oDatabase.DecryptedConnectionString)
            For Each oRow As DataRow In oTBT.Rows
                Dim oTable As SQLAutoDocLib.Table = oTableFactory.GetTableByName(lDatabaseID, oLogin.ID, oRow.Item("objname"))
                If Not oTable Is Nothing Then
                    oTable.Desc = oRow.Item("value")
                    oTable.Save()
                End If
            Next

            '---Get fields for tables
            For Each oTable As SQLAutoDocLib.Table In oTableFactory.GetAllTablesForDatabase(lDatabaseID)
                Dim oFLDT As DataTable = GetTableFieldDescriptions(oLogin.Login, oTable.Name, oDatabase.DecryptedConnectionString)
                For Each oRow As DataRow In oFLDT.Rows
                    Dim oField As SQLAutoDocLib.Field = oFieldFactory.GetFieldByName(oTable.ID, oRow.Item("objname"))
                    If Not oField Is Nothing Then
                        oField.Desc = oRow.Item("value")
                        oField.Save()
                    End If
                Next
            Next

            '---Get views for this login
            Dim oVBT As DataTable = GetViewDescriptions(oLogin.Login, oDatabase.DecryptedConnectionString)
            For Each oRow As DataRow In oVBT.Rows
                Dim oView As SQLAutoDocLib.View = oViewFactory.GetViewByName(lDatabaseID, oLogin.ID, oRow.Item("objname"))
                If Not oView Is Nothing Then
                    oView.Desc = oRow.Item("value")
                    oView.Save()
                End If
            Next

            '---Get procedures for this login
            Dim oSBT As DataTable = GetProcedureDescriptions(oLogin.Login, oDatabase.DecryptedConnectionString)
            For Each oRow As DataRow In oSBT.Rows
                Dim oSProc As SQLAutoDocLib.SProc = oSProcFactory.GetSProcByName(lDatabaseID, oLogin.ID, oRow.Item("objname"))
                If Not oSProc Is Nothing Then
                    oSProc.Desc = oRow.Item("value")
                    oSProc.Save()
                End If
            Next

            '---Get functions for this login
            Dim oFBT As DataTable = GetFunctionDescriptions(oLogin.Login, oDatabase.DecryptedConnectionString)
            For Each oRow As DataRow In oFBT.Rows
                Dim oFunction As SQLAutoDocLib.Function = oFunctionFactory.GetFunctionByName(lDatabaseID, oLogin.ID, oRow.Item("objname"))
                If Not oFunction Is Nothing Then
                    oFunction.Desc = oRow.Item("value")
                    oFunction.Save()
                End If
            Next
        Next
    End Sub

    Private Function GetDatabaseDescription( _
                            ByVal sConnection As String) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("default,")
            .Append("default,")
            .Append("default,")
            .Append("default,")
            .Append("default,")
            .Append("default)")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString, sConnection)
    End Function

    Private Function GetLoginDescriptions( _
                        ByVal sConnection As String) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("'USER',")
            .Append("default,")
            .Append("default,")
            .Append("default,")
            .Append("default,")
            .Append("default)")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString, sConnection)
    End Function

    Private Function GetTableDescriptions( _
                            ByVal sOwner As String, _
                            ByVal sConnection As String) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("'user',")
            .Append("'" & sOwner.Replace("'", "''") & "',")
            .Append("'table',")
            .Append("default,")
            .Append("default,")
            .Append("default)")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString, sConnection)
    End Function

    Private Function GetTableFieldDescriptions( _
                        ByVal sOwner As String, _
                        ByVal sTableName As String, _
                        ByVal sConnection As String) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("'user',")
            .Append("'" & sOwner.Replace("'", "''") & "',")
            .Append("'table',")
            .Append("'" & sTableName.Replace("'", "''") & "',")
            .Append("'column',")
            .Append("default)")
            .Append(" where")
            .Append(" [name]='MS_Description'")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString, sConnection)
    End Function

    Private Function GetViewDescriptions( _
                        ByVal sOwner As String, _
                        ByVal sConnection As String) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("'user',")
            .Append("'" & sOwner.Replace("'", "''") & "',")
            .Append("'view',")
            .Append("default,")
            .Append("default,")
            .Append("default)")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString, sConnection)
    End Function

    Private Function GetProcedureDescriptions( _
                        ByVal sOwner As String, _
                        ByVal sConnection As String) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("'user',")
            .Append("'" & sOwner.Replace("'", "''") & "',")
            .Append("'procedure',")
            .Append("default,")
            .Append("default,")
            .Append("default)")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString, sConnection)
    End Function

    Private Function GetFunctionDescriptions( _
                    ByVal sOwner As String, _
                    ByVal sConnection As String) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("'user',")
            .Append("'" & sOwner.Replace("'", "''") & "',")
            .Append("'function',")
            .Append("default,")
            .Append("default,")
            .Append("default)")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString, sConnection)
    End Function

End Class
